﻿using System;
using System.Data;
using System.Data.SqlClient;

namespace SJC
{
    internal class DBHelper
    {
        private static SqlConnection connection;
        public static SqlConnection Connection
        {
            get
            {
                string sqlcon = "Data Source=.;Initial Catalog=JDGL;Integrated Security=true";
                // 数据库连接的不同可能的状态
                if (connection == null)
                {
                    //不存在连接时
                    connection = new SqlConnection(sqlcon);
                    connection.Open();
                }
                else if (connection.State == System.Data.ConnectionState.Broken)
                {
                    //连接断开时
                    connection.Close();
                    connection.Open();
                }
                else if (connection.State == System.Data.ConnectionState.Closed)
                {
                    //连接关闭时
                    connection.Open();
                }
                return connection;
            }
        }
        //改写CURD方法
        //查找一条记录使用，返回具体信息（包含多个列信息）
        public static SqlDataReader Cha_b(string sql)
        {
            SqlCommand c = new SqlCommand(sql, Connection);
            return c.ExecuteReader();
        }
        //查找多条记录，返回查询到的第一行第一列的数据
        public static int Cha_a(string sql)
        {
            SqlCommand c = new SqlCommand(sql, Connection);
            return (int)c.ExecuteScalar();//查
        }
        //sql语句中字符串变量多时使用
        public static int ZSG_b(string sql, params SqlParameter[] para)
        {
            SqlCommand c = new SqlCommand(sql, Connection);
            c.Parameters.AddRange(para);
            return c.ExecuteNonQuery();//增删改
        }
        //sql语句中字符串变量少时使用
        public static int ZSG_a(string sql)
        {
            SqlCommand c = new SqlCommand(sql, Connection);
            return c.ExecuteNonQuery();//增删改
        }

        //适配器、数据集、sql命令构建器
        public static SqlDataAdapter dataAdapter;
        public static DataSet dataSet;
        public static SqlCommandBuilder sqlBuilder;
        //自动生成sql语句更新数据库方法
        public static int UpdateSqlDBBuilder(string tableName)
        {
            sqlBuilder = new SqlCommandBuilder(dataAdapter);
            int Ri= dataAdapter.Update(dataSet, tableName);
            sqlBuilder.RefreshSchema();
            return Ri;
        }
        //显示当前表格数据方法
        public static DataTable ShowDataTable(string sqlCommand,string tableName)
        {
            dataSet = new DataSet();
            dataAdapter = new SqlDataAdapter(sqlCommand, Connection);
            dataAdapter.Fill(dataSet,tableName);
            DataTable dt;
            dt = dataSet.Tables[tableName];
            return dt;
        }

    }
}
